typedstream IBObjectData Object CustomObject PasteFormula Responder ScrollView ClipView ciifffcfffs [118c]{\rtf0\ansi{\fonttbl\f1\fmodern Courier;} \margl40 \margr40 \f1\b0\i0\ulnone\ql\fs24\fi0\li0\gray0\fc0\cf0\up0\dn0 NXCursor NXImage NXibeam Scroller Control _doScroller: @@@ffs NXBrowser @@::#@ssssi** Matrix NXBrowserCell BrowserItem Helvetica Button ButtonCell ActionCell Paste MenuTemplate *@*@ccc OtherViews @:@iiii [15@] MenuCell @Math Financial @Financial String @String Constants @Constants Special @Special @Date Database @Database Operators ff@@#::s PopUpList popUp: NXpopup Functions Helvetica-Bold edit.formulabuilder Title NXStringTable HashTable ABS(num) AND(value[,value...]) CLEAN( CLEAN(string) @IF(exp,value1[,value2]) RIGHT( RIGHT(str,num) DDB(cst,slvg,life,per) @ISERR( @ISERR(exp) FORMAT( FORMAT(num, fmt-const [,prec]) @DATEVALUE( @DATEVALUE(date-str) @ISFORMULA( @ISFORMULA(adr) @CTERM( @CTERM(rate,fv,pv) REPEAT( REPEAT(string,times) @ISREF( @ISREF(exp) @LOWER( @LOWER(str) @COUNT( @COUNT(num|range[,...]) MINUTE( MINUTE(date) @ROUND( @ROUND(num,prec) @VALUE( @VALUE(string) DCOUNT( DCOUNT(database,offset,crit) ISTEXT( ISTEXT(exp) @DPROD( @DPROD(database,offset,crit) SECOND( SECOND(date) IFELSE( IFELSE(exp,value1,value2) @RANGE( @RANGE(ur,lc,lr,rc) @UPPER( @UPPER(str) SIGNAL( SIGNAL(cond,named_port,range) CHOOSE( CHOOSE(num,item1[,item2[,...]) LENGTH( LENGTH(string) DOLLAR( DOLLAR(num[,prec]) @MONTH( @MONTH(date) PV(rate,pers,pmnt[,fv[,type]]) PROPER( PROPER(str) BEEPIF( BEEPIF(cond) OR(value[,value...]) STDDEV( STDDEV(num|range[,...]) ISERROR( ISERROR(exp) DSTDEV( DSTDEV(database,offset,crit) @HISTOGRAM( ,@HISTOGRAM(range,divisions,anchor[,min,max]) @EXACT( @EXACT(str1,str2) FV(rate,pers,pmt[,pv[,type]]) @NEXTMONTH( @NEXTMONTH(date) LN(num) @INDEX( @INDEX(range,col,row) @ISSTRING( @ISSTRING(exp) VLOOKUP( VLOOKUP(code,table,offset) @S(exp) @TIMEVALUE( @TIMEVALUE(time-str) IF(exp,value1[,value2]) @CLEAN( @CLEAN(string) @N(exp) @RIGHT( @RIGHT(str,num) @STRING( @STRING(num,prec) ADDRESS( ADDRESS(row,col) @CONVDATE( @CONVDATE(date_number) @SETCOLOR( @SETCOLOR(value,red,green,blue) @FORMAT( @FORMAT(num, fmt-const [,prec]) @@(string) DATEVALUE( DATEVALUE(date-str) ISFORMULA( ISFORMULA(adr) HLOOKUP( HLOOKUP(code,table,offset) @REPEAT( @REPEAT(string,times) ISEMPTY( ISEMPTY(adr) @MINUTE( @MINUTE(date) @NEXTYEAR( @NEXTYEAR(date_ser) @DCOUNT( @DCOUNT(database,offset,crit) ISSTEXT( ISTEXT(adr) @SECOND( @SECOND(date) @CALCRATE( %@CALCRATE(present-value,payment,term) WEEKDAY( WEEKDAY(date-ser) @SIGNAL( @SIGNAL(cond,named_port,range) @GETINPUT( @GETINPUT(cell) @CHOOSE( @CHOOSE(num,item1[,item2[,...]) @LENGTH( @LENGTH(string) REPLACE( REPLACE(str,start,num,new-str) ISBLANK( ISBLANK(adr) @DEGTORAD( @DEGTORAD(num) @ISNUMBER( @ISNUMBER(exp) WORKDAY( !WORKDAY(date-ser,days[,holidays]) @RADTODEG( @RADTODEG(num) @PROPER( @PROPER(str) AVERAGE( AVERAGE(num|range[,...]) @BEEPIF( @BEEPIF(cond) HISTOGRAM( +HISTOGRAM(range,divisions,anchor[,min,max]) DSTDDEV( DSTDDEV(database,offset,crit) NEXTMONTH( NEXTMONTH(date) ATAN2( ATAN2(x-num,y-num) ISSTRING( ISSTRING(exp) @VLOOKUP( @VLOOKUP(code,table,offset) T(exp) TIMEVALUE( TIMEVALUE(time-str) N(exp) ROWS( ROWS(range) SQRT( SQRT(num) ROOT( ROOT(num,root) @ADDRESS( @ADDRESS(row,col) CONVDATE( CONVDATE(date_number) SETCOLOR( SETCOLOR(value,red,green,blue) TRIM( TRIM(string) REPT( REPT(string,times) DSUM( DSUM(database,offset,crit) @HLOOKUP( @HLOOKUP(code,table,offset) INDIRECT( INDIRECT(ref) @ISEMPTY( @ISEMPTY(adr) NEXTYEAR( NEXTYEAR(date_ser) @SUM( @SUM(num|range[,...]) PROD( PROD(num|range[,...]) @NPV( @NPV(rate,num|range[,...]) @SETALTCOLOR( "@SETALTCOLOR(value,red,green,blue) LOG10( LOG10(num) @NOT( @NOT(num) @PMT( @PMT(prin,int,term) COLS( COLS(range) SIGN( SIGN(num) DAYS( DAYS(date-str | num) @SYD( @SYD(cst,slvg,life,per) NEXT( NEXT(value[,inc]) @CELLPOINTER( @CELLPOINTER(atrb) TIME( TIME(hr,min,sec) HOUR( HOUR(date) CALCRATE( $CALCRATE(present-value,payment,term) @EXP( @EXP(num) @IRR( @IRR(guess,num|range[,...]) @SLN( @SLN(cst,slvg,life) DVAR( DVAR(database,offset,crit) @NETWORKDAYS( $@NETWORKDAYS(date1,date2[,holidays]) @WEEKDAY( @WEEKDAY(date-ser) @INT( @INT(num) ASIN( ASIN(num) @STD( @STD(num|range[,...]) GETINPUT( GETINPUT(cell) DMAX( DMAX(database,offset,crit) @SIN( @SIN(num) @VAR( @VAR(num|range[,...]) @REPLACE( @REPLACE(str,start,num,new-str) @SGN( @SGN(num) DMIN( DMIN(database,offset,crit) DEGTORAD( DEGTORAD(num) SAME( SAME(adr) @MAX( @MAX(num|range[,...]) ACOS( ACOS(num) ISNUMBER( ISNUMBER(exp) DAVERAGE( DAVERAGE(database,offset,crit) @WORKDAY( "@WORKDAY(date-ser,days[,holidays]) @COS( @COS(num) RADTODEG( RADTODEG(num) @MIN( @MIN(num|range[,...]) ATAN( ATAN(num) @TAN( @TAN(num) @SETBKGCOLOR( "@SETBKGCOLOR(value,red,green,blue) @DIV( @DIV(num,div) @LOG( @LOG(num) FIND( FIND(sub-str,str,start) YEAR( YEAR(date) DAVE( DAVE(database,offset,crit) @MOD( @MOD(num,div) CELL( CELL(atrb,adr) @LEN( @LEN(string) DATE( DATE(yr,mon,day) @DAY( @DAY(date) CHAR( CHAR(num) @AVG( @AVG(num|range[,...]) @AVE( @AVE(num|range[,...]) FRAC( FRAC(num) ISNA( ISNA(exp) CODE( CODE(string) LEFT( LEFT(str,num) @MID( @MID(str,start,len) @ATAN2( @ATAN2(y-num,x-num) @ABS( @ABS(num) @ROWS( @ROWS(range) @SQRT( @SQRT(num) @DDB( @DDB(cst,slvg,life,per) ISERR( ISERR(exp) @ROOT( @ROOT(num,root) @TRIM( @TRIM(string) ISREF( ISREF(exp) LOWER( LOWER(str) COUNT( COUNT(num|range[,...]) @DSUM( @DSUM(database,offset,crit) ROUND( ROUND(num,prec) @TERM( @TERM(pmt,rate,fv) SUM(num|range[,...]) VALUE( VALUE(string) @PROD( @PROD(num|range[,...]) NPV(rate,pmt1[,pmt2[,...]]) SETALTCOLOR( !SETALTCOLOR(value,red,green,blue) @SIGN( @SIGN(num) @COLS( @COLS(range) NOT(num) PMT(rate,pers,pv[,fv[,type]]) DPROD( DPROD(database,offset,crit) SYD(cst,slvg,life,per) CELLPOINTER( CELLPOINTER(atrb) @TIME( @TIME(hr,min,sec) @NEXT( @NEXT(value[,inc]) @DSTD( @DSTD(database,offset,crit) @HOUR( @HOUR(date) EXP(num) SLN(cst,slvg,life) IRR(guess,num|range[,...]) @DVAR( @DVAR(database,offset,crit) NETWORKDAYS( #NETWORKDAYS(date1,date2[,holidays]) RANGE( RANGE(ur,lc,lr,rc) UPPER( UPPER(str) @RATE( @RATE(fv,pv,per) @ASIN( @ASIN(num) INT(num) SIN(num) @DMAX( @DMAX(database,offset,crit) VAR(num|range[,...]) SGN(num) @DMIN( @DMIN(database,offset,crit) MAX(num|range[,...]) @ACOS( @ACOS(num) @SAME( @SAME(adr) MONTH( MONTH(date) @PV(pmt,rate,per) COS(num) MIN(num|range[,...]) @ATAN( @ATAN(num) TAN(num) SETBKGCOLOR( !SETBKGCOLOR(value,red,green,blue) DIV(num,div) LOG(num) STDEV( STDEV(num|range[,...]) @DAVG( @DAVG(database,offset,crit) @FIND( @FIND(sub-str,str,start) @YEAR( @YEAR(date) @CELL( @CELL(atrb,adr) MOD(num,div) @DAVE( @DAVE(database,offset,crit) LEN(string) DAY(date) @CHAR( @CHAR(num) @DATE( @DATE(yr,mon,day) EXACT( EXACT(str1,str2) AVE(num|range[,...]) @FRAC( @FRAC(num) @FV(pmt,rate,per) @CODE( @CODE(string) @LEFT( @LEFT(str,num) @ISNA( @ISNA(exp) @LN(num) MID(str,start,len) FIXED( FIXED(num,prec) INDEX( INDEX(range,row,col) WindowTemplate iiii***@s@ Formula Builder DPPanel @ISERR Returns 1 if exp returns an error other than @NA. Returns 0 otherwise. @ISERR(@ERR) -> 1 (true) @ISERR(@VALUE("ABCD") -> 1 (true) : This formula tried to convert "ABCD" to a value using @VALUE(). This returns !ERR, which causes @ISERR() to return 1. Returns the day of the month for a date serial number. A3: 18-Jul-92 =DAY(A3) -> 18 =DAY(NOW) : This formula will return the current day of the month. LENGTH Returns the number of characters in the string. LENGTH (``10% discount on COD'') = 19 A1= ``A short string '', B1=``sentence'' LENGTH(A1) = 14 LENGTH(A1&B1) = 22 @COUNT Counts the number of numeric or string entries. Cells or items containing strings are counted. Cells or items containing blanks are not counted. A B 1 Duck 10 2 Soup 20 3 1.86 20 4 $300 10 5 '====== 30 6 50 7 40 70 @COUNT(A1:A7) = 6: There are only six items in column A because cell A6 is blank. @COUNT(A1:A7,B1:B7) = 13: This counts the 6 items in column A and adds the 7 items in column B. @COUNT("Good","Bad","Indifferent") = 3 Note: This function differs from COUNT(num|range[,...]). @COUNT() counts string entries. COUNT() ignores string entries and returns the count of cells that contain numeric values. wReturns the Absolute Value of the number. ABS(-45.3) = 45.3 ABS(COS(PI)) = 1 ABS(34) = 34 ABS(``Absolutely!'') = 0 DCOUNT Counts all the numeric items matching criteria in the column offset from the upper left corner of the database range. This is a sample database: A B C D 1 NAME GRADE GPA GRADE 2 Joe B 3.2 B 3 Sue A 4.7 4 Robert C 2.7 5 Steve A 4.5 6 Ann B 4.2 The database range is A1:C6. The criteria range is D1:D2. To count the number of students who earned B's use this formula: =DCOUNT(A1:C6,1,D1:D2) -> 2 @DIDCHANGE .Returns a 1 if the sheet has changed since the last time it was saved, and a 0 if nothing has changed. Since the act of adding this function changes the sheet, it will only show as true except when the sheet has been recalculated right after it has been saved. This can be useful in MScript scripts. @NEXTMONTH Returns a date serial number one calendar month after date. If the day of the month is greater than 28, it is set to 28. A3: 27-Jul-92 @NEXTMONTH(A3) -> 8274 : This is the date-number for 27-Aug-92. @NEXTMONTH(11381) = 11409 : This is the date-number for 28-Mar-2001. USERNAME Returns the user's login name. PATHNAME Returns the current path name. INDIRECT Evaluates string as a cell address and returns that address. A3: Cheers B3: A3 INDIRECT(B3) -> Cheers A1: 10 A2: 20 A3: 30 B1: A1:A3 @SUM(INDIRECT (B1)) -> 60 @BEEPIF Beeps if the condition is true and returns 1, 0 if false. C7: 100 @BEEPIF(C7<200) : This formula will beep one time. @BEEPIF(@rand>0.5) : This formula will generate a random number between 0 and 1. If the number is > 0.5, the it beeps and returns 1. Otherwise it returns 0. @DAYMONFORMAT cconstant is used to specify date day-month format. @FORMAT(9876.54321, @DAYMONTHFORMAT) -> 15-Jan Returns the number of rows in a range. =ROWS(A1:H20) -> 20 If RANGE1 is the name assigned to the range B1:CC200, =ROWS(RANGE1) -> 200 @SETALTCOLOR Sets the alternate color of the current cell and returns value. Red, Green, and Blue are numbers between 0 and 1 that set the percentage of red, green, and blue that will be mixed together to create the color. White is all ones and black is all zeros. If any of the color components are out of bounds or result in errors, the color is not changed. @SETALTCOLOR(A1,@IF(A1 < -100,1,0),0,0) : This formula sets the alternate color to red if the number is less than -100, otherwise sets the alternate color to black. CONVDATE Converts a number in the format YYMMDD to a date serial number. Dates after the year 2000 can be specified by placing a 1 before the year. =CONVDATE(930210) -> 8441 : This is the date-number of 10-Feb-93 =CONVDATE(1100410) -> 14709 : This returns the date number of 10-Apr-2010. PERCENTFORMAT aThis constant is used to specify the percent format. =FORMAT(0.09389, PERCENTFORMAT, 2) -> 9.39% @COLS Returns the number of columns in a range. @COLS(A1:H20) -> 8 If RANGE1 is the name assigned to the range B1:CC200, @COLS(RANGE1) -> 80 DKGRAYCOLOR [A constant referring to Dark GRAY for use in functions that change color (e.g. SETCOLOR()) DINTL1FORMAT yThis constant is used to specify the numeric date month-day-year format. =FORMAT(9876.54321, DINTL1FORMAT) -> 01/15/97 @CURRENCYFORMAT bThis constant is used to specify the currency format. @FORMAT(555.9, @CURRENCYFORMAT) -> $555.90 FIXEDFORMAT bThis constant is used to specify the fixed decimal format. =FORMAT(42.692, FIXEDFORMAT) -> 42.69 @RATE Returns the interest rate for an investment of pv to achieve a value of fv in per periods. How much interest would an a bank account need to earn for an investment of $67,000 to grow to $100,000 in 5 years or less? @RATE(100000,67000,5) -> 8.34% or more. Returns the fractional component of num. =FRAC(NOW) : This formula returns only the time component of the current date. =FRAC(3.14159) -> 0.14159 (Returns the current date serial number. JReturns the area of a circle with a radius of 1, or approximately 3.1415. @HOURMINFORMAT lThis constant is used to specify date hours-minutes format. @FORMAT(9876.54321, @HOURMINFORMAT) -> 1:02 PM @CODE Returns the ASCII code for the first character in string. @CODE("A") -> 65 @CODE("{") -> 123 @CODE("Hello") -> 72 : This formula is returning the ASCII code for "H" Returns the logarithm, base 10, of num. @LOG(45) = 1.6532 +10^(@LOG(292)) -> 292: This formula takes the base 10 log of 292, then raises 10 to that power, resulting in the number it began with. Takes the average of numeric records that match criteria. This is a sample database: A B C D 1 NAME GRADE GPA GRADE 2 Joe B 3.2 B 3 Sue A 4.7 4 Robert C 2.7 5 Steve A 4.5 6 Ann B 4.2 The database range is A1:C6. The criteria range is D1:D2. To calculate the average GPA of everyone who earned a B use this formula: =DAVE(A1:C6,2,D1:D2) -> 3.7 @DATE MReturns a date serial number for the year, month and day. A date serial number is the number of days since January 1, 1970. Dates after the year 2000 can be specified by placing a 1 before the year. @DATE(92,8,24) -> 8271 : This is the date number for 24-Aug-92. @DATE(79,10,31) -> 3590 : This is the date-number for 31-Oct-79. Returns the Arccosine of num (in radians). ACOS(COS(num)) = num. What is the angle with the cosine = 0.7071? =ACOS(0.7071) -> 0.7854 (radians) : This is equivalent to an angle of 45 degrees. =ACOS(0.7071)*180/pi -> 45 : This gives the same answer as above, but in degrees. TINTL1FORMAT {This constant is used to specify twenty four hour time format with seconds. =FORMAT(9876.54321, TINTL1FORMAT) -> 13:02:13 CReturns the Cosine of num. @COS(@PI) -> -1 @COS(@LN(3)) -> 0.4548 Divides the sum of the numbers by the count of numeric and string entries, using the same rules for counting as @COUNT(). @AVE(4,5,25,3) = 9.25 Given the following data: A1: 184 A2: 592 A3: 97 @AVE(A1:A3) = 291 Note: This function differs from AVE(num|range[,...]) or AVERAGE(num|range[,...]). @AVE() and @AVG() treat string entries as having a zero value, but AVE() and AVERAGE() ignore string entries. @HISTOGRAM uGroups the values in range into #-of-divisions divisions, counts the number of items in each division. The formula should appear in #-of-divisions cells, starting at anchor. It returns the number of items in the division that is offset from the anchor point. A B 1 Name GPA 2 Joe 2.6 3 Sue 3.7 4 Robert 3.2 5 Steve 3.0 6 Ann 3.8 7 John 3.5 8 Mary 2.9 9 Chris 3.5 To divide the students into 5 divisions by GPA and determine how many fall into each division: A10: =HISTOGRAM($B$2:$B$9,5,$A$10) -> 1 B10: =SAME($A$10) -> 2 C10: =SAME($A$10) -> 1 D10: =SAME($A$10) -> 2 E10: =SAME($A$10) -> 2 @TIMEVALUE YConverts a string to a date serial number. A1: '10 A2: '58 A3: '29 @TIMEVALUE(A1&":"&A2&":"&A3) -> 0.45728 : This formula takes the string contents of cells A1, A2 & A3 and concatenates them into the string "10:58:29", then converts that string to a time value. A3: 12:34 am, @TIMEVALUE(A3) -> 0.023611 : This is the date-number for 12:34 am. PROPER Converts str to "proper" capitalization. It will turn the first letter of each word to upper case; otherwise, it converts the letter to lower case. =PROPER("MS. JANET KRAFT") -> Ms. Janet Kraft =PROPER("all entries posted before 7/2") -> All Entries Posted Before 7/2 A bit of history: Upper and lower case does not refer to the size of the letters, but to where they were stored in type cabinets. The capital letters were stored in the "Upper Case," while the small letters were stored in the "Lower Case." @ISSTRING Returns 1 is exp results in a string. Returns 0 otherwise. @ISSTRING(@VALUE("340")) -> 0 @ISSTRING(@LEFT("Goodbye!",4)) -> 1 @ISSTRING("Some text") -> 1 BEEPIF Beeps and returns 1 if the condition is true, returns 0 if false. C7: 100 =BEEPIF(C7<200) : This formula will beep one time. =BEEPIF(rand>0.5) : This formula will generate a random number between 0 and 1. If the number is > 0.5, the it beeps and returns 1. Otherwise it returns 0. @SETCOLOR Sets the color of the current cell and returns value. Red, Green, and Blue are numbers between 0 and 1 that set the percentage of red, green, and blue that will be mixed together to create the color. White is all ones and black is all zeros. If any of the color components are out of bounds or result in errors, the color is not changed. @SETCOLOR(A1,@IF(A1 > 100,1,0),@IF(A1 > 100,0,1),0) : This formula sets the color to red if the number is greater than 100, otherwise sets the color to green. REPLACE Replaces num characters in str starting offset characters from the first character with the string new-str. =REPLACE("The cost is $450, COD",13,3,"2,500") -> The cost is $2,500, COD =REPLACE("Robert J. Stevenson",7,1,"L") -> Robert L. Stevenson @ATAN2 !Returns the Arctangent of x/y. This gives the angle of a line from the origin to x,y. @ATAN2(3,1) -> 0.3217 (radians): This is equivalent to an angle of 18.4 degrees. This function differs from ATAN2(x,y). @ATAN2() requires the Y parameter first. ATAN2() requires the X parameter first. @TODAY YReturns the current date serial number without the current time (only the current date.) @DKGRAYCOLOR \A constant referring to Dark GRAY for use in functions that change color (e.g. @SETCOLOR()) @ISEMPTY @Returns 1 if the cell at adr contains no value, else returns 0. @WHITECOLOR XA constant referring to WHITE for use in functions that change color (e.g. @SETCOLOR()) @REPEAT Returns string repeated times times. @REPEAT("X",10) -> XXXXXXXXXX @REPEAT("over and ",4) -> over and over and over and over and Returns the square root of the number. =SQRT(34) -> 5.8310 =SQRT(LOG(200)) -> 1.5169 =SQRT(VAR(200,500,100)) -> 170 : This formula computes the standard deviation of the values given in the variance function. @ROWS Returns the number of rows in a range. @ROWS(A1:H20) -> 20 If RANGE1 is the name assigned to the range B1:CC200, @ROWS(RANGE1) -> 200 @ROOT /Returns the nth root of num. @ROOT(81,4) -> 3 TRUE() Returns a logical true value. CALCRATE XCalculates the interest rate such that present-value = @PV(payment,interest-rate,term). ISREF Returns 1 if exp is a cell or range reference. Returns 0 otherwise. =ISREF(A1) -> 1 (true) If RANGE1 has been assigned A1:C10, =ISREF(RANGE1) -> 1 EXACT 0Returns 1 if the strings match exactly. Returns 0 otherwise. =EXACT("Daytime","Daytyme") -> 0 (false) A1="hello" and B1="hello": =EXACT(A1,B1) -> 1 (true) Note: When you use = to match strings, the comparison ignores case. For example, "Upper" = "upper" is true, but EXACT("Upper", "upper") is false. @TRIM Removes leading and trailing spaces from string. It also converts multiple consecutive spaces in string to single spaces. A3: Account # 3040302 40303 @TRIM(A3) -> Account # 3040302 40303 @TRIM(" Anything at all.") -> Anything at all. @NEXTYEAR Returns the next year of the date. If the date is 2/29 of a leap year, the date returned is 2/28 of the following year. @NEXTYEAR(@NOW) : This formula returns a date-number one year from the current date. @NEXTYEAR(@DATE(93,6,20)) -> 8936 : This is the date-number for 20-Jun-94. @SIGN Returns 0 if num is zero, -1 if num is less than 0, and 1 if num is greater than 0. B7: @RAND @SIGN(B7-0.5) : This formula returns the sign of a randomly generated number between 0.5 and -0.5. @SIGN(-405) -> -1 AVERAGE ~Divides the sum of the numbers by the count of numeric entries. Uses the same rules for counting as COUNT(). =AVERAGE(4,5,25,3) -> 9.25 Given the following data: A1: 184 A2: 592 A3: 97 =AVE(A1:A3) -> 291 Note: This function is different than @AVE(num|range[,...]) or @AVG(num|range[,...]). AVE() and AVERAGE() ignore string values. @AVE() and @AVG() treat string values as zero. @DINTL2FORMAT pThis constant is used to specify the numeric date month-day format. @FORMAT(9876.54321, @DINT2FORMAT) -> 01/15 @DVAR Returns the variance of the records that match the criteria. Strings have a value of zero. This is a sample database: A B C D 1 STATE REGION AVG RF REGION 2 AL South 3.7 West 3 CA West 2.1 South 4 NJ East 5.2 5 FL South 4.9 6 LA South 6.8 7 TX West 5.0 8 MA North 7.6 9 AZ West 2.2 10 NV West 2.4 The database range is A1:C10. The criteria range is D1:D3. To find the variance of rainfall in the Southern and Western regions use this formula: @DVAR(A1:C10,2,D1:D3) -> 2.7192 INDEX Returns the cell that is row rows and col columns offset from the upper left corner of range. A B C 1 10 20 30 2 3942 4932 5929 3 4920 5929 9294 =INDEX(A1:C3,2,2) -> 9294 A10: 1 A11: 0 =INDEX(A1:C3,A10,A11) -> 3942 Note: This function is different from @INDEX(range,col,row). INDEX() specifies the offset in row then column order. @INDEX() specifies the offset in column then row order. Returns the Sum of the Years Digits depreciation for an asset that cost cst, has a salvage value of slvg, a life of life in period per. A company buys a delivery van for $12,000. It has a useful life of 5 years and will be worth $2,000 at the end of that time. How much depreciation can the company write off in years 1,2 and 4? year 1: =SYD(12000,2000,5,1) -> $3,333 year 2: =SYD(12000,2000,5,2) -> $2,667 year 4: =SYD(12000,2000,5,4) -> $1,333 @ISNUMBER 0Returns 1 if exp returns a numeric result. Returns 0 otherwise. @ISNUMBER(@STRING(300,0)) -> 0 : This formula converts the number "300" to a string using @STRING(). It then uses @ISNUMBER() to test if that result is a value and returns 0 (false). @ISNUMBER(34) -> 1 A3: +394 - 100 @ISNUMBER(A3) -> 1 FReturns a random number between 0 and 1, including 0 and excluding 1. TODAY @CHAR 5Returns the ASCII character for num. @CHAR(68) -> D MReturns the straight-line depreciation of an asset that cost cst, has a salvage value of slvg, and a life of life. A company buys a delivery van for $12,000. It has a useful life of 5 years and will be worth $2,000 at the end of that time. How much depreciation can the company write off each year? =SLN(12000,2000,5) -> $2,000 3Computes the Net Present Value of an investment with a given stream of income at a given interest rate. Each payment can be a value or a range. If it is a range, each of the numbers in the range is included in the calculation. An apartment building is being offered for sale for $400,000. Expected rental incomes for years 1-5 are $79,000, $85,000, $85,000, $103,000 and $120,000 and the interest rate is expected to stay close to 7%. Is this a worthwhile investment? A 1 -400000 2 79000 3 85000 4 85000 5 103000 6 120000 To figure out the net present value of the cash in-flows: =NPV(7%,A2:A6) -> $381,596 To figure out the value of the project: Project Value = Initial Cost + NPV +A1+NPV(7%,A2:A6) -> -$18,404 No, this project is not worthwhile. Calculates the payment required for a loan payoff. What would be the monthly payment on an $40,000 home improvement loan, to be repaid in 5 years at a fixed rate of 9%? =PMT(9%/12,5*12,-40000) -> $830.33 per month. UPPER Converts all the characters in str to upper case. =UPPER("Uppers only") -> UPPERS ONLY =UPPER(MID("robert c. smith",8,2)) -> C. : This formula uses MID() to return the 8th and 9th characters from the string "Robert C. Smith". The UPPER() function then capitalizes this result. @ATAN pReturns the Arctangent of num. @ATAN(5.6713) = 1.3962 (radians): This is equivalent to an angle of 80 degrees. nIf num is zero or not a number, 1 is the result; otherwise, 0 is the result. =NOT(34) -> 0 =NOT("Text") -> 1 Returns a string containing the first num characters from the left of str. =LEFT("The first twelve characters",12) -> The first tw =LEFT("ABCDEFG",4) -> ABCD @TINTL2FORMAT |This constant is used to specify twenty four hour time format without seconds. @FORMAT(9876.54321, @TINTL2FORMAT) -> 13:02 jReturns the variance of the numeric records that match the criteria. This is a sample database: A B C D 1 STATE REGION AVG RF REGION 2 AL South 3.7 West 3 CA West 2.1 South 4 NJ East 5.2 5 FL South 4.9 6 LA South 6.8 7 TX West 5.0 8 MA North 7.6 9 AZ West 2.2 10 NV West 2.4 The database range is A1:C10. The criteria range is D1:D2. To find the variance of rainfall in the Southern and Western regions use this formula: =DVAR(A1:C10,2,D1:D3) -> 2.7192 STDEV Results in the standard deviation of the ranges or list. Blank cells and strings are not counted. A B 1 NAME SCORE 2 Anna 65.8 3 Bill 95.4 4 Donna 30.2 5 Mark 54.9 6 Maria 35.1 7 Susan 75.9 8 John 83.2 9 Rob 33.1 10 Ethan 81.8 =STDEV(B2:B10) -> 23.1 Note: This functions are different than @STD(num|range[,...]). STDDEV() and STDEV() ignore string values. @STD() treats string values as zero. @MONYEARFORMAT hThis constant is used to specify date month-year format. @FORMAT(9876.54321, @MONYEARFORMAT) -> Jan-97 &Calculates the internal rate of return of an investment. A rug-cleaning business is being offered for sale for $180,000. The owner says the business should net $25,000, $35,000, $39,000, $40,000 and $55,000 in years 1-5. What is this project's internal rate of return? A 1 -180000 2 25000 3 35000 4 39000 5 40000 6 55000 The initial expense is entered as a negative number in cell A1. The range of cash inflows are given in cells A2:A6. To calculate this project's IRR value: =IRR(B1,A1:A6) -> 2.3% Returns the Arcsine of num (in radians). ASIN(SIN(num)) = num. What is the angle whose sine = 0.3090? =ASIN(0.3090) -> 0.3141 (radians) : This is equivalent to an angle of 18 degrees. =ASIN(0.3090)*180/pi -> 18 : This gives the same answer as above, but in degrees. Returns the smallest numeric item in the records that match the criteria. This is a sample database: A B C D 1 NAME SALES REGION REGION 2 Joe 3000 East East 3 Sue 4500 North 4 Robert 2800 East 5 Steve 3700 South 6 Ann 2400 West The database range is A1:C6. The criteria range is D1:D2. To find the smallest sales figure in the Eastern region use this formula: =DMIN(A1:C6,1,D1:D2) = 2800 cReturns the location of sub-str in str. It starts searching for sub-str offset characters from the left of str. Returns an error if the string is not found. =FIND("hampton","Northampton",1) -> 4 =FIND("hampton","Northampton",6) -> !ERR (because it starts searching at the 6th character) A1: "The third word in this sentence." =FIND("word",A1,1) -> 10 Returns the remainder of num divided by div. =MOD(12,5) -> 2 =NOW-MOD(NOW,7)+5 : This function returns the date-number for Monday of the current week.. >Computes the Sine of num (expressed in radians). @SIN(60*PI/180) -> 0.8660 : This formula gives the sine of 60 degrees. A mountain road goes up at an incline of 25 degrees. If the road is straight and is 20 miles long, what vertical distance will a car travel to climb it? +20 * @SIN(25*(@pi/180)) -> 8.45 miles The result is the statistical variance of the numbers. Strings are counted as 0, and blank cells are not counted. A B 1 NAME SCORE 2 Anna 65.8 3 Bill 95.4 4 Donna 30.2 5 Mark 54.9 6 Marie 35.1 7 Susan 75.9 8 John 83.2 9 Rob 33.1 10 Ethan 81.8 @VAR(B2:B10) = 531.6010 Note: This function differs from VAR(num|range[,...]). @VAR() counts string values as zero, but VAR() ignores string values. CELLPOINTER Returns the value for the attribute of the currently active cell after a recalculation of the sheet. This is valuable for macros. See CELL() or @CELL() for information about attributes. @CURRENTVALUE The current value of the cell (before the recalc). This is useful for comparing old cell values with other values, such as taking the larger of the current value or the value of another cell. Results in the largest number in the range or list. =MAX(4.5,3.2,2.5,2.5,6.2) -> 6.2 =MAX(4,4,4) -> 4 A B C 1 300 400 2 0 400 3 200 100 4 300 5000 5 700 6 600 7 300 8 500 =MAX(A1:A8,C1:C8) -> 5000 Note: This function is different than @MAX(num|range[,...]). MAX() ignores string values. @MAX() treats string values as zero. @FRAC Returns the fractional component of num. @FRAC(@NOW) : This formula returns only the time component of the current date. @FRAC(3.14159) -> 0.14159 Returns a string that is len characters taken from str starting start characters from the left of str. =MID(``Have a great day!'',8,5) -> great A1 = ``Robert Quimby Douglas'', =MID(A1,8,6) -> Quimby Returns e to the num power. EXP(LN(num)) = num. @EXP(4) -> 54.5982 @EXP(12%*5) * 30000 -> $54,664: This formula calculates the present value of $30,000 invested at 12% continually compounded interest for a term of 5 years. Returns the integer portion of num. @INT(@RAND*10) : This formula returns a random number between 0 and 9. A3: 68293 @INT(@LOG(A3))+1 -> 5 : This formula tells how many digits are the number in cell A3. Returns the smallest number in the range or list. String values are considered to be zero. A1: 300 A2: 400 A3: Hello! @MIN(A1:A3)-> 0 : This formula returns zero because the string "Hello!" evaluates to zero. A1: 0.0002 A2: 0 A3: -339492 @MIN(A1:A3) -> -339492 Note: This function differs from MIN(num|range[,...]). @MIN() treats string value as zero, but MIN() ignores string values. `Returns an error. All cells that refer to a cell with an error will evaluate to errors as well. 5Returns the ASCII character for num. =CHAR(68) -> D THISCOLUMN @Returns the column number of the cell containing this constant. @DATEVALUE Converts a date string to a date serial number. If the value is a number, just returns the number. @DATEVALUE("02/04/92") -> 8069 A3: 'Jul-94 @DATEVALUE(A3) -> 8947 : This is the date serial number for 1-Jul-94 Computes the present value of per payments of pmt with a rate of rate. What is the present value of the retirement annuity which promises to pay $30,000 every year for the next 20 years, given an interest rate of 12%? @PV(30000,12%,20) -> $224,083.31 LOG10 Returns the logarithm, base 10, of num. =LOG10(45) -> 1.6532 +10^(LOG(292)) -> 292 : This formula takes the base 10 log of 292, then raises 10 to that power, resulting in the number it began with. Returns the natural logarithm of num. @LN(27) -> 3.2958 B8: 1024 @LN(B8)/@LN(2) -> 10 : This formula returns the base 2 log of cell B8. Computes the future value of per payments of pmt with the interest rate of rate. If $2,000 were deposited every year into an account earning 9% annual interest, how much would the account be worth in 20 years? @FV(2000,9%,20) = $102,320.24 Returns the Double Declining Balance depreciation for an asset that cost cst, has a salvage value of slvg, a life of life in period per. A company buys a delivery van for $12,000. It has a useful life of 5 years and will be worth $2,000 at the end of that time. How much depreciation can the company write off in years 1,2 and 3? year 1: =DDB(12000,2000,5,1) -> $4,800 year 2: =DDB(12000,2000,5,2) -> $2,880 year 3: =DDB(12000,2000,5,3) -> $1,728 @CTERM Computes the number of periods required for an investment of pv to grow to fv at rate. How long will it take for a $20,000 investment to grow to $40,000 at an annual rate of 5%? @CTERM(0.05,20000,40000) -> 14.2 years How many months will it take for a $4,000 investment to grow to $8,000, being compounded monthly at a rate of 8%? @CTERM(8%/12,4000,8000) -> 104 months (about 8 1/2 years) @LOWER YConverts all the characters in str to lower case. @LOWER("Bill Johnson") = bill johnson FORMAT This function returns the given number num in the format specified by the format constant fmt-const. You may optionally specify the precision prec, the number of decimal places in the number. =FORMAT(18.9, SCIENTIFICFORMAT) -> 1.89e+01 ISSTRING Returns 1 is exp results in a string. Returns 0 otherwise. =ISSTRING(VALUE("340")) -> 0 =ISSTRING(LEFT("Goodbye!",4)) -> 1 =ISSTRING("Some text") -> 1 Returns a special error, NA. All cells that refer to a cell with an error will evaluate to errors as well. @NA is useful to designate cells that have a value that is not available or defined. @PERCENTFORMAT bThis constant is used to specify the percent format. @FORMAT(0.09389, @PERCENTFORMAT, 2) -> 9.39% @EXACT &Returns 1 if the strings match exactly, 0 otherwise. @EXACT("Daytime","Daytyme") -> 0 (false) A1: "hello" B1: "hello" @EXACT(A1,B1) -> 1 (true) Note: When you use = to match strings, the comparison ignores case. For example, "Upper" = "upper" is true, but @EXACT("Upper", "upper") is false. If exp is a string, returns exp; otherwise it returns a zero length string. This function is used to guarantee that an expression is a string. @S(343) -> "" (blank string) A1: 2000 A2: Orders @S(A1)&@S(A2) = Orders @DCOUNT Counts all the numeric and string items matching criteria in the column offset from the upper left corner of the database range. This is a sample database: A B C D 1 NAME GRADE GPA GRADE 2 Joe B 3.2 B 3 Sue A 4.7 4 Robert C 2.7 5 Steve A 4.5 6 Ann B 4.2 The database range is A1:C6. The criteria range is D1:D2. To count the number of students who earned B's use this formula: @DCOUNT(A1:C6,2,D1:D2) -> 2 @VLOOKUP Looks up code in the vertical table and returns the cell offset cells from the left side of the first match. If the first column contains only strings and code is a number, it returns the value from the last row. If code is also a string, but doesn't match any of the table entries, !ERR is returned. A B 1 A 5.0 2 B 4.0 3 C 3.0 4 D 2.0 5 F 0 @VLOOKUP("A",A1:B5,1) -> 5.0 @VLOOKUP("D",A1:B5,1) -> 2.0 @VLOOKUP("Incomplete",A1:B5,1) -> !ERR @VLOOKUP(3.0,A1:B5,1) -> 0 COUNT Counts the number of numeric entries. Cells or items containing blanks or strings are not counted. A B 1 Duck 10 2 Soup 20 3 1.86 20 4 $300 10 5 '====== 30 6 50 7 40 70 =COUNT(A1:A7) -> 3 : There are only three items in column A because cells A1, A2, and A5 are strings and A6 is blank. =COUNT(A1:A7,B1:B7) -> 10 : This counts the 3 items in column A and adds the 7 items in column B. =COUNT("Good","Bad","Indifferent",0) -> 1 This function is different than @COUNT(num|range[,...]). COUNT() only counts numeric values. @COUNT() also counts string values. greater than or equal to ROUND Rounds num to prec decimal places. If prec is less than zero, rounds to the left of the decimal place. =ROUND(350.2852,2) -> 350.29 =ROUND(25492,-3) -> 25000 @ADDRESS iReturns the address for the row and col specified. @ADDRESS(0,0) is cell A1. B1: 15 @ADDRESS(0,1) -> 15 equal less than or equal to @HLOOKUP %Looks up code in the horizontal table. Returns the cell offset cells from the top of the first match. If the first row contains numbers and code is less than the first value in the lookup row, !ERR is returned. If code is greater than the first value but no exact match is found, it returns the value from the last column. If the first row contains only strings and no exact match is found, it returns !ERR. A B C 1 1 2 3 2 $200 $40 $500 @HLOOKUP(2,A1:B3,1) -> $400 @HLOOKUP(0.5,A1:B3,1) -> !ERR @HLOOKUP(5,A1:B3,1) -> $500 @FIXEDFORMAT cThis constant is used to specify the fixed decimal format. @FORMAT(42.692, @FIXEDFORMAT) -> 42.69 @REPLACE Replaces num characters in str starting at character start with the string new-str. @REPLACE("The cost is $450, COD",13,3,"2,500") -> The cost is $2,500, COD @REPLACE("Robert J. Stevenson",7,1,"L") -> Robert L. Stevenson NACONST HOSTNAME 8Returns the name of the machine that Mesa is running on. NEXTMONTH Returns a date serial number one calendar month after date. If the day of the month is greater than 28, then it is set to 28. A3: 27-Jul-92 =NEXTMONTH(A3) -> 8274 : This is the date-number for 27-Aug-92. =NEXTMONTH(11381) -> 11409 : This is the date-number for 28-Mar-2001. Returns a date serial number for a given time. =TIME(11,30,00) -> 0.479167 : This is the date-number for 11:30 AM. =NOW+TIME(8,0,0) : This formula returns a date-number for the time 8 hours from now. @TERM @TERM() computes the number of payments of pmt required to reach fv with a interest rate of rate. How many $700 monthly payments would be needed to accumulate $250,000 in a bank account earning 4% interest? @TERM(700,4%/12,250000) -> 235.6 monthly payments (taking almost 20 years). @CALCRATE Takes the formula in cell adr and executes it as if it had been copied and then pasted into the current cell. This is a powerful and valuable function. You can have a master function and a series of functions that are the same. Where you change the master function, the method of calculation used by the other functions is changed. You can use SAME() in conjunction with INDEX(), HLOOKUP(), VLOOKUP(), and to define a formula used in a calculation rather than having a complex IF() statement. An application for this is an employee pay calculation that depends on an employee type. A1: 1 A2: 2 If B1: =A1 * 5 and B2: =SAME(B1), then B2 = 10 (A2*5). If B1: =A1/5, then B2 changes to 0.4 (A2/5) If B1: =SIN(A1), B2 changes to 0.9093 (SIN(A2)) CURRENTVALUE SETBKGCOLOR Sets the background color of the current cell and returns value. Red, Green, and Blue are numbers between 0 and 1 that set the percentage of red, green, and blue that will be mixed together to create the color. White is all ones and black is all zeros. If any of the color components are out of bounds or result in errors, the color is not changed. =SETBKGCOLOR(A1,IF(A1 > 50 && A1 < 100,1,0),0,0) : This sets the background color to red if the number is greater than 50 and less than 100, otherwise it sets the background color to black. Sums all the items matching criteria in the column offset from the upper left corner of the database range. This is a sample database: A B C D 1 STATE REGION AVG RF REGION 2 AL South 3.7 South 3 CA West 2.1 4 NJ East 5.2 5 FL South 4.9 6 LA South 6.8 7 TX West 5.0 8 MA North 7.6 9 AZ West 2.2 10 NV West 2.4 The database range is A1:C10. The criteria range is D1:D2. To find the total amount of rainfall for the Southern region use this formula: =DSUM(A1:C10,2,D1:D2) -> 15.4 COMMAFORMAT \This constant is used to specify the comma format. =FORMAT(238.093, COMMAFORMAT) -> 238.09 MONTH Returns the month for a date serial number. =MONTH(DATEVALUE("12/24/92")) -> 12 =CHOOSE(MONTH(NOW)-1,"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec") : This formula will display the current month. Adds all the numbers or sums the numbers in the range. You can specify as many numbers or ranges as you want. @SUM(300,400,2300,100) -> 3100 A1: Blue A2: 300 A4: 900 B1: 1200 @SUM(A1:A4,B1) -> 2400 not equal to logical AND Returns the day of the month for a date serial number. A3: 18-Jul-92 @DAY(A3) -> 18 @DAY(@NOW) : This formula will return the current day of the month. #AND# NETWORKDAYS Returns the number of working days between the date1 serial number and date2. Working days are all days other than Saturday, Sunday, and the optional holidays listed. =NETWORKDAYS(NOW,NOW+10) : This returns the number of working days between now and 10 days from now. tReturns the Absolute Value of num. @ABS(-45.3) -> 45.3 @ABS(@COS(@PI))-> 1 @ABS(34) -> 34 @ABS("Absolutely!") -> 0 @BLUECOLOR WA constant referring to BLUE for use in functions that change color (e.g. @SETCOLOR()) @CURRENTNUMBER The current numeric value of a cell. Can be used for time series functions. To remember the largest number entered into cell A1, use @if(a1 > @currentnumber,a1,@currentnumber). To count the number of times the worksheet was recalculated, use @currentnumber + 1 DMYFORMAT nThis constant is used to specify the date day-month-year format. =FORMAT(9876.54321, DMYFORMAT) -> 15-Jan-97 STDDEV @RANGE Returns the address for the range specified by the upper row, left column, lower row, right column.. @RANGE(0,0,1,1) is the range a1:b2. @ISREF Returns 1 if exp is a cell or range reference. Returns 0 otherwise. @ISREF(A1) -> 1 (true) If RANGE1 has been assigned A1:C10, @ISREF(RANGE1) -> 1 @INDEX Returns the cell that is row rows and col columns offset from the upper left corner of range. A B C 1 10 20 30 2 3942 4932 5929 3 4920 5929 9294 @INDEX(A1:C3,2,2) = 9294 A10: 0 A11: 1 @INDEX(A1:C3,A10,A11) = 3942 Note: This function is different from INDEX(range,row,col). INDEX() specifies the offset in row then column order. @INDEX() specifies the offset in column then row order. @UPPER Converts all the characters in str to upper case. @UPPER("Uppers only") -> UPPERS ONLY @UPPER(@MID("robert c. smith",7,2)) -> C. : This formula uses @MID() to return the 8th and 9th characters from the string "Robert C. Smith". The @UPPER() function then capitalizes this result. @STRING DRounds num to prec decimal places and converts it to a string. B3: 5251.769585987 @STRING(B3,6) -> 5251.769586 @STRING(3/43,2)&" is now a string" -> 0.07 is now a string : This formula evaluates 3/43, then converts it to a string with 2 decimal places of precision and concatenates it with the text " is now a string". logical OR @COMMAFORMAT ]This constant is used to specify the comma format. @FORMAT(238.093, @COMMAFORMAT) -> 238.09 @FORMAT This function returns the given number num in the format specified by the format constant fmt-const. You may optionally specify the precision prec, the number of decimal places in the number. @FORMAT(18.9, SCIENTIFICFORMAT) -> 1.89e+01 CYANCOLOR VA constant referring to CYAN for use in functions that change color (e.g. SETCOLOR()) ADDRESS hReturns the address for the row and col specified. ADDRESS(0,0) is cell A1. B1: 15 =ADDRESS(0,1) -> 15 DAYMONFORMAT bconstant is used to specify date day-month format. =FORMAT(9876.54321, DAYMONTHFORMAT) -> 15-Jan CURRENCYFORMAT aThis constant is used to specify the currency format. =FORMAT(555.9, CURRENCYFORMAT) -> $555.90 @MAGENTACOLOR ZA constant referring to MAGENTA for use in functions that change color (e.g. @SETCOLOR()) @DMAX Returns the maximum value of the records in database that match criteria. Strings have a value of zero. This is a sample database: A B C D 1 NAME SALES REGION REGION 2 Joe 3000 East East 3 Sue 4500 North 4 Robert 2800 East 5 Steve 3700 South 6 Ann 2400 West The database range is A1:C6. The criteria range is D1:D2. To find the largest sales figure in the Eastern region use this formula: @DMAX(A1:C6,1,D1:D2) -> 3000 MAGENTACOLOR YA constant referring to MAGENTA for use in functions that change color (e.g. SETCOLOR()) Multiplies all the numbers in the parenthesis, or all the numbers in the range. You may specify a number of different ranges. =PROD(3, 5, 10, 2) -> 300 A1: 3 A2: 5 B1: 10 =PROD(A1:A2, B1, 2) -> 300 @HOUR Returns the hour for a date serial number. @HOUR(@TIMEVALUE("23:00:00")) -> 23 @HOUR(@NOW) : This formula will return the current hour of day. @DINTL1FORMAT zThis constant is used to specify the numeric date month-day-year format. @FORMAT(9876.54321, @DINTL1FORMAT) -> 01/15/97 @DSUM Sums all the items matching criteria in the column offset from the upper left corner of the database range. This is a sample database: A B C D 1 NAME SALES REGION REGION 2 Joe 3000 East East 3 Sue 4500 North 4 Robert 2800 East 5 Steve 3700 South 6 Ann 2400 West The database range is A1:C10. The criteria range is D1:D2. To find the total amount of rainfall for the Southern region use this formula: @DSUM(A1:C10,2,D1:D2) -> 15.4 @ASIN Returns the Arcsine of num (in radians). ASIN(SIN(num)) = num. What is the angle whose sine = 0.3090? @ASIN(0.3090) -> 0.3141 (radians): This is equivalent to an angle of 18 degrees. @ASIN(0.3090)*180/@pi -> 18: This gives the same answer as above, but in degrees. @DMIN Returns the smallest numeric item in the records that match the criteria. String values will count as zeros. This is a sample database: A B C D 1 NAME SALES REGION REGION 2 Joe 3000 East East 3 Sue 4500 North 4 Robert 2800 East 5 Steve 3700 South 6 Ann 2400 West The database range is A1:C6. The criteria range is D1:D2. To find the smallest sales figure in the Eastern region use this formula: @DMIN(A1:C6,1,D1:D2) -> 2800 CLEAN ,Removes all control characters from string. @PROD Multiplies all the numbers in the parenthesis, or all the numbers in the range. You may specifie a number of different ranges. @PROD(3, 5, 10, 2) -> 300 A1: 3 A2: 5 B1: 10 @PROD(A1:A2, B1, 2) -> 300 Note that cells containing strings are counted as zero. @TINTL1FORMAT |This constant is used to specify twenty four hour time format with seconds. @FORMAT(9876.54321, @TINTL1FORMAT) -> 13:02:13 @SAME Takes the formula in cell adr and executes it as if it had been copied and then pasted into the current cell. This is a powerful and valuable function. You can simply have a master formula and a series of formulas that are the same. Where you change the master formula, the method of calculation used by the other formulas is changed. You can use @SAME() in conjunction with @INDEX(), @HLOOKUP(), and @VLOOKUP() to define a formula used in a calculation rather than having a complex @IF() statement. An application for this is an employee pay calculation that depends on an employee type. A1: 1 A2: 2 If B1: =A1 * 5 and B2: @SAME(B1), then B2 -> 10 (A2*5). If B1: =A1/5, then B2 changes to 0.4 (A2/5) If B1: @SIN(A1), B2 changes to 0.9093 (@sin(A2)) Returns the Sum of the Years Digits depreciation for an asset that cost cst, has a salvage value of slvg, a life of life in period per. A company buys a delivery van for $12,000. It has a useful life of 5 years and will be worth $2,000 at the end of that time. How much depreciation can the company write off in years 1, 2 and 4? year 1: @SYD(12000,2000,5,1) -> $3,333 year 2: @SYD(12000,2000,5,2) -> $2,667 year 4: @SYD(12000,2000,5,4) -> $1,333 JReturns the straight-line depreciation of an asset that cost cst, has a salvage value of slvg, and a life of life. A company buys a delivery van for $12,000. It has a useful life of 5 years and will be worth $2,000 at the end of that time. How much depreciation can the company write off each year? @SLN(12000,2000,5) -> $2,000 DPROD Returns the product of all the items matching the criteria in the column offset from the upper left corner of the range. Strings are ignored. nIf num is zero or not a number, 1 is the result; otherwise, 0 is the result. @NOT(34) -> 0 @NOT("Text") -> 1 Computes the internal rate of return of an investment. String entries are considered zero values. A rug-cleaning business is being offered for sale for $180,000. The owner says the business should net $25,000, $35,000, $39,000, $40,000 and $55,000 in years 1-5. What is this project's internal rate of return? A 1 -180000 2 25000 3 35000 4 39000 5 40000 6 55000 The initial expense is entered as a negative number in cell A1. The range of cash inflows are given in cells A2:A6. To calculate this project's IRR value: @IRR(B1,A1:A6) -> 2.3% Note: This differs from IRR(guess,num|range[,...]). @IRR() counts string entries as zero, but IRR() ignores string entries. Returns a string that is len characters taken from str starting start characters from the left of str. @MID("Have a great day!",8,5) = great A1: "Robert Quimby Douglas", @MID(A1,8,6) = Quimby MReturns the Cosine of num (in radians). =COS(PI) -> -1 =COS(LN(3)) -> 0.4548 Returns the Double Declining Balance depreciation for an asset that cost cst, has a salvage value of slvg, a life of life in period per. A company buys a delivery van for $12,000. It has a useful life of 5 years and will be worth $2,000 at the end of that time. How much depreciation can the company write off in years 1,2 and 3? year 1: =DDB(12000,2000,5,1) -> $4,800 year 2: =DDB(12000,2000,5,2) -> $2,880 year 3: =DDB(12000,2000,5,3) -> $1,728 @ROUND Rounds num to prec decimal places. If prec is less than zero, it rounds to the left of the decimal place. @ROUND(350.2852,2) -> 350.29 @ROUND(25492,-3) -> 25000 SETCOLOR Sets the color of the current cell and returns value. Red, Green, and Blue are numbers between 0 and 1 that set the percentage of red, green, and blue that will be mixed together to create the color. White is all ones and black is all zeros. If any of the color components are out of bounds or result in errors, the color is not changed. =SETCOLOR(A1,IF(A1 > 100,1,0),IF(A1 > 100,0,1),0) : This sets the color to red if the number is greater than 100, otherwise it sets the color to green. @GENERALFORMAT bThis constant is used to specify the general format. @FORMAT(238.093, @GENERALFORMAT) -> 238.093 REDCOLOR UA constant referring to RED for use in functions that change color (e.g. SETCOLOR()) WEEKDAY Returns the day of the week for a date serial number, 0 - Sunday through 6 - Saturday. =WEEKDAY(NOW-5) : This returns the day of the week 5 days prior to today. A7: "6/20/82" =WEEKDAY(DATEVALUE(A7)) -> 0 : Returns the day of the week for 20-Jun-82. @PROPER Converts str to "proper" capitalization. It will turn the first letter of each word to upper case; otherwise, it converts the letter to lower case. @PROPER("MS. JANET KRAFT") = Ms. Janet Kraft @PROPER("all entries posted before 7/2") = All Entries Posted Before 7/2 YELLOWCOLOR XA constant referring to YELLOW for use in functions that change color (e.g. SETCOLOR()) @CONVDATE Converts a number in the format YYMMDD to a date serial number. Dates after the year 2000 can be specified by placing a 1 before the year. @CONVDATE(930210) -> 8441 : This is the date-number of 10-Feb-93 @CONVDATE(1100410) -> 14709 : This returns the date number of 10-Apr-2010. THISROW =Returns the row number of the cell containing this constant. @HOSTNAME @WORKDAY Returns the date serial number days working days from the date serial number. Working days are all days other than Saturday, Sunday, and the optional holidays listed. @WORKDAY(@NOW,10) : This returns the date serial number of the day 10 working days from now. @NEXT Returns the next logical value after value. For example, if value = "Mon", then @NEXT() returns "Tue". Values for months, days, and quarters are built in. If value is a number, @NEXT() adds 1 or inc to the number. For quarterly information, it will increment the quarter. When the quarter reaches 4, the next quarter is Q1 of the next year. For example, @NEXT("Q4 `92") is "Q1 `93". @NEXT("Q4 '92") = Q1 '93 @NEXT("January") = February @NEXT("Region 1") = Region 2 GETINPUT 2Returns the user-typed input string for the cell. DINTL2FORMAT oThis constant is used to specify the numeric date month-day format. =FORMAT(9876.54321, DINT2FORMAT) -> 01/15 Returns the interest rate for an investment of pv to achieve a value of fv in per periods. How much interest would an a bank account need to earn for an investment of $67,000 to grow to $100,000 in 5 years or less? RATE(100000,67000,5) = 8.34% or more. Returns 1 if exp returns an NA error. Returns 0 otherwise. =ISNA(@NA) -> 1 (true) A1: 34 A2: 49 A3: =NA =ISNA(SUM(A1:A3)) -> 1 : This formula returns 1 (true) because cell A3 contains NA, which causes SUM(A1:A3) to evaluate to NA also. TIMEVALUE WConverts a string to a date serial number. A1: '10 A2: '58 A3: '29 =TIMEVALUE(A1&":"&A2&":"&A3) = 0.45728 : This formula takes the string contents of cells A1, A2 & A3 and concatenates them into the string "10:58:29", then converts that string to a time value. A3: 12:34 am =TIMEVALUE(A3) -> 0.023611 : This is the date-number for 12:34 am. RAND() @CELL Returns information about a the cell at adr. atrb defines the type of information requested. @CELL("address",U7) = $U$7 @CELL("row",U7) = 7 @CELL("col",U7) = 21 U7 = @PI, @CELL("contents",U7) = 3.141593 ADDRESS - returns the address of the cell ROW - returns the row of the cell COL - returns the column of the cell CONTENTS - returns the contents of the cell FORMAT - returns the format of the cell. Formats are: G - General Fn - Fixed Decimal n places Sn - Scientific Format n decimal places Cn - Currency format n decimal places ,n - Comma format n decimal places + - Chart Format Pn - Percent Format n decimal places T - Text Format H - Hidden Format D1 - Day Month Year Format D2 - Day Month Format D3 - Month Year Format D6 - Hours Minutes Seconds Format D7 - Hours Minutes Format D4 - Int'l Date Format 1 D5 - Int'l Date Format 2 D8 - Int'l Time Format 1 D9 - Int'l Time Format 2 PREFIX - The string prefix in Lotus Format PROTECT - Returns 0 if the cell is Unprotected, 1 otherwise TYPE - b - blank cell, v - number value, l - string value WIDTH - Returns the width of the column in characters RANGE - Converts a single address to a range #NOT# This is the logical NOT function, but it is not used the same way as the logical AND and OR. For example, #NOT# 1 will equal 0, while #NOT# 0 will equal 1. A3 = +("Blue"="Red") , which returns 0 #NOT#A3 = 1 : The formula in cell A3 asks "is the string 'Blue' = the string 'Red'? The answer is 0 (false). The #NOT# function inverts this returning the answer 1 (true) #NOT#1 = 0 (false) @DSTD Returns the standard deviation of the records that match the criteria. Strings have a value of zero. This is a sample database: A B C D 1 NAME SALES REGION REGION 2 Joe 3000 East East 3 Sue 4500 North 4 Robert 2800 East 5 Steve 3700 South 6 Ann 2400 West The database range is A1:C10. The criteria range is D1:D2. To find the standard deviation of rainfall in the Western region use this formula: @DSTD(A1:C10,2,D1:D2) -> 1.2029 RIGHT Returns a string containing the num characters from the right side of str. =RIGHT("The last ten characters",10) -> characters =RIGHT("Ida M. Brown",5) -> Brown Converts a date string to a date serial number. If the value is a number, just returns the number. =DAYS("02/04/92") = 8069 A3: 'Jul-94 =DAYS(A3) = 8947 : This is the date number for 1-Jul-94 @CELLPOINTER PReturns the value for the attribute of the currently active cell after a recalculation of the sheet. This is valuable for macros. ADDRESS - returns the address of the cell ROW - returns the row of the cell COL - returns the column of the cell CONTENTS - returns the contents of the cell FORMAT - returns the format of the cell. Formats are: G - General Fn - Fixed Decimal n places Sn - Scientific Format n decimal places Cn - Currency format n decimal places ,n - Comma format n decimal places + - Chart Format Pn - Percent Format n decimal places T - Text Format H - Hidden Format D1 - Day Month Year Format D2 - Day Month Format D3 - Month Year Format D6 - Hours Minutes Seconds Format D7 - Hours Minutes Format D4 - Int'l Date Format 1 D5 - Int'l Date Format 2 D8 - Int'l Time Format 1 D9 - Int'l Time Format 2 PREFIX - The string prefix in Lotus Format PROTECT - Returns 0 if the cell is Unprotected, 1 otherwise TYPE - b - blank cell, v - number value, l - string value WIDTH - Returns the width of the column in characters RANGE - Converts a single address to a range Returns the ASCII code for the first character in string. CODE(``A'') = 65 CODE(``{'') = 123 CODE(``Hello'') = 72 : This formula is returning the ASCII code for ``H'' MReturns a date serial number for the year, month and day. A date serial number is the number of days since January 1, 1970. Dates after the year 2000 can be specified by placing a 1 before the year. =DATE(92,8,24) -> 8271 : This is the date number for 24-Aug-92. =DATE(79,10,31) -> 3590 : This is the date-number for 31-Oct-79. TINTL2FORMAT {This constant is used to specify twenty four hour time format without seconds. =FORMAT(9876.54321, TINTL2FORMAT) -> 13:02 LTGRAYCOLOR \A constant referring to Light GRAY for use in functions that change color (e.g. SETCOLOR()) @VALUE @Returns the number value of string. @VALUE("3.14159") -> 3.14159 @VALUE("1200") + 2000 -> 3200 @VALUE("320/104") -> !ERR Note: This function differs from VALUE(string). If the argument to @VALUE() cannot be completely converted to a number, it returns an error, while VALUE() returns as much of the number as it can. ATAN2 4Returns the Arctangent of x/y. This gives the angle of a line from the origin to x,y. =ATAN2(1,3) -> 0.3218 (radians) : This is equivalent to an angle of 18.4 degrees. Note: This function is different than @ATAN2(y,x). ATAN2() requires the X parameter to be first. @ATAN2() requires the Y parameter first. IFELSE Evaluates exp. If it is a non-zero value, the result is value1. If it is a zero or a string, value2 is the result. =IFELSE(5>10,"Something is wrong!","Everything is normal.") -> Everything is normal. A1: 3400 A2: 4000 A3: 5000 =IFELSE(SUM(A1:A3)>40000,"Full Capacity","Okay") -> Okay @CYANCOLOR WA constant referring to CYAN for use in functions that change color (e.g. @SETCOLOR()) @FALSE Returns a logical false value. @MINUTE Returns the minute for a date serial number. @MINUTE(@TIMEVALUE("11:35:20")) -> 35 @MINUTE(@NOW) : This formula will return the current number of minutes past the hour. ISFORMULA AReturns 1 if the cell at adr contains a formula, else returns 0. @DPROD Returns the product of all the items matching the criteria in the column offset from the upper left corner of the range. Strings are treated as zero values. GREENCOLOR WA constant referring to GREEN for use in functions that change color (e.g. SETCOLOR()) HOURMINFORMAT kThis constant is used to specify date hours-minutes format. =FORMAT(9876.54321, HOURMINFORMAT) -> 1:02 PM @REDCOLOR VA constant referring to RED for use in functions that change color (e.g. @SETCOLOR()) ISBLANK @SCIENTIFICFORMAT oThis constant is used to specify the scientific notation format. @FORMAT(1.69, @SCIENTIFICFORMAT) -> 1.89e+01 @PATHNAME DAVERAGE CHARTFORMAT UThis constant is used to specify the chart format. =FORMAT(7, CHARTFORMAT) -> +++++++ SCIENTIFICFORMAT nThis constant is used to specify the scientific notation format. =FORMAT(1.69, SCIENTIFICFORMAT) -> 1.89e+01 Returns the year for a date serial number. =YEAR(NOW-1000) : This returns the year of the date 1,000 days prior to the current date. A7: '6/20/82 =YEAR(DATEVALUE(A7)) -> 82 : This formula extract the year from the date-string "6/20/82". SETALTCOLOR Sets the alternate color of the current cell and returns value. Red, Green, and Blue are numbers between 0 and 1 that set the percentage of red, green, and blue that will be mixed together to create the color. White is all ones and black is all zeros. If any of the color components are out of bounds or result in errors, the color is not changed. =SETALTCOLOR(A1,IF(A1 < -100,1,0),0,0) : This sets the alternate color to red if the number is less than -100, otherwise it sets the alternate color to black. exclusive OR Returns string repeated times times. =REPEAT("X",10) -> XXXXXXXXXX =REPT("over and ",4) -> over and over and over and over and Returns 0 if num is zero, -1 if num is less than 0, and 1 if num is greater than 0. B7: +RAND =SIGN(B7-0.5) : This formula returns the sign of a randomly generated number between 0.5 and -0.5. =SGN(-405) -> -1 VALUE Returns the number value of string. If string begins with a number but contains non-numbers, VALUE() returns as much as it can. If string begins with a letter, it returns 0. =VALUE("3.14159") = 3.14159 =VALUE("1200") + 2000 = 3200 =VALUE("320/104") = 320 Note: This function is different than @VALUE(string). If the argument to VALUE() cannot be completely converted to a number, it returns as much of the number as possible. @VALUE() returns an error. Returns the next logical value after value. For example, if value = "Mon", then NEXT() returns "Tue". Values for months, days, and quarters are built in. If value is a number, NEXT() adds 1 or inc to the number. For quarterly information, it will increment the quarter. When the quarter reaches 4, the next quarter is Q1 of the next year. For example, NEXT("Q4 '92") is "Q1 '93". =NEXT("Q4 '92") -> Q1 '93 =NEXT("January") -> February =NEXT("Region 1") -> Region 2 @NETWORKDAYS Returns the number of working days between the date1 serial number and date2. Working days are all days other than Saturday, Sunday, and the optional holidays listed. @NETWORKDAYS(@NOW,@NOW+10) : This returns the number of working days between now and 10 days from now. Returns the hour for a date serial number. =HOUR(TIMEVALUE("23:00:00")) -> 23 =HOUR(NOW) : This formula will return the current hour of day. @ACOS Returns the Arccosine of num (in radians). ACOS(COS(num)) = num. What is the angle with the cosine = 0.7071? @ACOS(0.7071) -> 0.7854 (radians): This is equivalent to an angle of 45 degrees. @ACOS(0.7071)*180/@pi -> 45: This gives the same answer as above, but in degrees. @TRUE WHITECOLOR WA constant referring to WHITE for use in functions that change color (e.g. SETCOLOR()) FILENAME Returns the current file name. @CHARTFORMAT VThis constant is used to specify the chart format. @FORMAT(7, @CHARTFORMAT) -> +++++++ [Computes the Sine of the number. The number is expressed in radians. =SIN(60*PI/180) = 0.8660 : This formula gives the sine of 60 degrees. A mountain road goes up at an incline of 25 degrees. If the road is straight and is 20 miles long, what vertical distance will a car travel to climb it? The formula is +20 * SIN(25*(pi/180)) -> 8.45 miles The result is the statistical variance of the numbers. Strings and blank cells are not counted. A B 1 NAME SCORE 2 Anna 65.8 3 Bill 95.4 4 Donna 30.2 5 Mark 54.9 6 Marie 35.1 7 Susan 75.9 8 John 83.2 9 Rob 33.1 10 Ethan 81.8 =VAR(B2:B10) -> 531.6010 Note: This function is different than @VAR(num|range[,...]). VAR() ignores string values. @VAR() treats string values as zero. @BLACKCOLOR XA constant referring to BLACK for use in functions that change color (e.g. @SETCOLOR()) ?Returns the Tangent of num (in radians). =TAN(1.24) -> 2.9119 CURRENTNUMBER The current numeric value of a cell. Can be used for time series functions. To remember the largest number entered into cell A1, use =if(a1 > currentnumber,a1,currentnumber). To count the number of times the worksheet was recalculated, use =currentnumber + 1 Results in the standard deviation of the ranges or list. Strings are counted as 0, and blank cells are not counted. A B 1 NAME SCORE 2 Anna 65.8 3 Bill 95.4 4 Donna 30.2 5 Mark 54.9 6 Maria 35.1 7 Susan 75.9 8 John 83.2 9 Rob 33.1 10 Ethan 81.8 @STD(B2:B10) -> 23.1 Note: This function differs from STDDEV(num|range[,...]) or STDEV(num|range[,...]). @STD() counts strings as zero, but STDDEV() and STDEV() ignore string values. @FILENAME Returns e to the num power. EXP(LN(num)) = num. =EXP(4) -> 54.5982 =EXP(12%*5) * 30000 = $54,664 : This formula calculates the present value of $30,000 invested at 12% continually compounded interest for a term of 5 years. Returns the integer portion of the number num. =INT(RAND*10) : This formula returns a random number between 0 and 9. A3: 68293 +INT(LOG(A3))+1 -> 5 : This formula tells how many digits are the number in cell A3. FIXED BRounds num to prec decimal places and converts it to a string. B3: 5251.769585987 =FIXED(B3,6) = 5251.769586 =FIXED(3/43,2)&" is now a string" -> 0.07 is now a string : This formula evaluates 3/43, then converts it to a string with 2 decimal places of precision and concatenates it with the text " is now a string". gResults in the smallest number in the range or list. A1: 300, A2: 400, A3: Hello! =MIN(A1:A3) -> 300 : This formula returns 300, and the string entry in cell A3 is ignored. A1: 0.0002, A2: 0, A3: -339492 +MIN(A1:A3) -> -339492 Note: This function is different than @MIN(num|range[,...]). MIN() ignores string values. @MIN() treats string values as zero. Returns the Arctangent of num (in radians). ATAN(TAN(num)) = num =ATAN(5.6713) -> 1.3963 (radians) : This is equivalent to an angle of 80 degrees. ?Returns the Tangent of num (in radians). @TAN(1.24) -> 2.9119 Returns the number of characters in the string. LENGTH("10% discount on COD") = 19 A1: "A short string " B1: "sentence" =LEN(A1) -> 15 =LENGTH(A1&B1) -> 23 Returns information about a the cell at adr. Atrb defines the type of information requested. U7: PI =CELL("address",U7) -> $U$7 =CELL("row",U7) -> 7 =CELL("col",U7) -> 21 =CELL("contents",U7) -> 3.141593 ADDRESS - returns the address of the cell ROW - returns the row of the cell COL - returns the column of the cell CONTENTS - returns the contents of the cell FORMAT - returns the format of the cell. Formats are: G - General Fn - Fixed Decimal n places Sn - Scientific Format n decimal places Cn - Currency format n decimal places ,n - Comma format n decimal places + - Chart Format Pn - Percent Format n decimal places T - Text Format H - Hidden Format D1 - Day Month Year Format D2 - Day Month Format D3 - Month Year Format D6 - Hours Minutes Seconds Format D7 - Hours Minutes Format D4 - International Date Format 1 D5 - International Date Format 2 D8 - International Time Format 1 D9 - International Time Format 2 PREFIX - The string prefix in Lotus Format PROTECT - Returns 0 if the cell is Unprotected, 1 otherwise TYPE - b - blank cell, v - number value, l - string value WIDTH - Returns the width of the column in characters RANGE - Converts a single address to a range Returns the number of characters in string. @LENGTH("10% discount on COD") -> 19 A1: "A short string " B1: "sentence" @LEN(A1) = 14 @LENGTH(A1&B1) = 22 zReturns the integer portion of the division of num divided by div. This complements the @MOD() function. @DIV(12,5) -> 2 yReturns the integer portion of the division of num divided by div. This complements the MOD() function. =DIV(12,5) -> 2 @GREENCOLOR XA constant referring to GREEN for use in functions that change color (e.g. @SETCOLOR()) MINUTE Returns the minute for a date serial number. =MINUTE(TIMEVALUE("11:35:20")) -> 35 =MINUTE(NOW) : This formula will return the current number of minutes past the hour. @HMSFORMAT tThis constant is used to specify date hours-minutes-seconds format. @FORMAT(9876.54321, @HMSFORMAT) -> 1:02:13 PM OComputes the present value of an investment with a given interest rate, number of periods, and periodic payment. If a future value is not given, it is assumed to be 0. If type is not zero, payments are assumed to start in the next period. What is the present value of the retirement annuity which promises to pay $30,000 every year for the next 20 years, given an interest rate of 12%? =PV(12%,20,-30000) -> $224,083.31 What is the present value of the investment which promises to pay $225,000 at the end of 20 years, given an interest rate of 12%? =PV(12%,20,,-225000) -> $23,325.02 VLOOKUP Looks up code in the vertical table. Returns the cell offset cells from the left side of the first match. If the first column contains only strings and code is a number, it returns the value from the last row. If code is also a string, but doesn't match any of the table entries, !ERR is returned. A B 1 A 5.0 2 B 4.0 3 C 3.0 4 D 2.0 5 F 0 =VLOOKUP("A",A1:B5,1) -> 5.0 =VLOOKUP("D",A1:B5,1) -> 2.0 =VLOOKUP("Incomplete",A1:B5,1) -> !ERR =VLOOKUP(3.0,A1:B5,1) -> 0 REPEAT Returns string repeated times times. =REPEAT("X",10) -> XXXXXXXXXX =REPT("over and ",4) -> over and over and over and over and SECOND Returns the seconds of a date serial number. =SECOND(TIMEVALUE("10:31:19")) -> 19 =SECOND(0.26903) -> 24 : This formula returns the seconds for the date-value for 06:27:24 AM. SEvaluates exp. If it is a true or non-zero value, the result is value1. If it is false, zero, or a string, optional value2 is the result; otherwise, a blank is the result. @IF(5>10,"Something is wrong!","Everything is normal.") -> Everything is normal. A1: 3400 A2: 4000 A3: 5000 @IF(@SUM(A1:A3)>=12400,"Full Capacity") = Full Capacity CHOOSE Chooses the num item from the list. =CHOOSE(2,"first","second","third","fourth") -> third =CHOOSE(MOD(INT(NOW),7),"Thu","Fri","Sat",Sun","Mon","Tue","Wed") : This function returns the current day using NOW to provide the current date. Returns the natural logarithm (log base e) of num. =LN(27) -> 3.2958 B8: 1024 =LN(B8)/LN(2) -> 10 : This formula returns the base 2 log of cell B8. ISTEXT Returns 1 is exp results in a string. Returns 0 otherwise. =ISTEXT(VALUE("340")) -> 0 =ISTEXT(LEFT("Goodbye!",4)) -> 1 =ISTEXT("Some text") -> 1 Returns the Future Value of a stream of payments over pers periods at rate interest rate. If present value pv is not specified, it is assumed to be 0. Type designates when the payments start. If $2,000 were deposited every year into an account earning 9% annual interest, how much would the account be worth in 20 years? =FV(9%,20,-2000) -> $102,320.24 If a $50,000 were deposited instead and accrued 9% interest from the start of the year, what would the account be worth in 20 years? =FV(9%,20,,-50000,1) -> $ 280,220.54 ISEMPTY REvaluates exp. If it is a true or non-zero value, the result is value1. If it is false, zero, or a string, optional value2 is the result; otherwise, a blank is the result. =IF(5>10,"Something is wrong!","Everything is normal.") -> Everything is normal. A1: 3400 A2: 4000 A3: 5000 =IF(SUM(A1:A3)>=12400,"Full Capacity") = Full Capacity @USERNAME If exp is a number, @N() returns exp, otherwise @N() returns 0. @N() is guaranteed to return a number and is used in functions that require numbers as parameters. @N(@STRING(300,0)) -> 0 @N(34) -> 34 @RIGHT Returns a string containing the num characters from the right side of str. @RIGHT("The last ten characters",10) -> characters @RIGHT("Ida M. Brown",5) -> Brown @SECOND Returns the seconds of a date serial number. @SECOND(@TIMEVALUE("10:31:19")) -> 19 @SECOND(0.26903) -> 24 : This formula returns the seconds for the date-value for 06:27:24 AM. WORKDAY Returns the date serial number days working days from the date serial number. Working days are all days other than Saturday, Sunday, and the optional holidays listed. =WORKDAY(NOW,10) : This returns the date serial number 10 working days from now. @CHOOSE Chooses the item offset from the first item on the list. @CHOOSE(2,"first","second","third","fourth") -> third @CHOOSE(@MOD(@INT(@NOW),7),"Thu","Fri","Sat",Sun","Mon","Tue","Wed") : This function returns the current day using @NOW to provide the current date. @CLEAN RADTODEG fConverts num (in radians) to degrees. This complements the DEGTORAD() function. =RADTODEG(PI) -> 180 HLOOKUP )Looks up code in the horizontal table. Returns the cell offset cells from the top of the first match. If the first row contains numbers and code is less than the first value in the lookup row, !ERR is returned. If code is greater than the first value but no exact match is found, it returns the value from the last column. If the first row contains only strings and no exact match is found, it returns !ERR. A B C 1 1 2 3 2 $200 $40 $500 =HLOOKUP(2,A1:C2,1) -> $400 =HLOOKUP(0.5,A1:C2,1) -> !ERR =HLOOKUP(5,A1:C2,1) -> $500 @SIGNAL FIf the condition is true, sends range of cells to the named Mach port. MONYEARFORMAT gThis constant is used to specify date month-year format. =FORMAT(9876.54321, MONYEARFORMAT) -> Jan-97 Evaluates string as a cell address and returns that address. A3: Cheers B3: A3 @@(B3) -> Cheers A1: 10 A2: 20 A3: 30 B1: A1:A3 @SUM(@@(B1)) -> 60 ISERR Returns 1 if exp returns an error other than @NA. Returns 0 otherwise. =ISERR(ERRORCONST) -> 1 (true) =ISERR(@VALUE("ABCD") -> 1 (true) : This formula tried to convert "ABCD" to a value using @VALUE(). This returns !ERR, which causes ISERR() to returns 1. DATEVALUE Converts a date string to a date serial number. If the value is a number, just returns the number. =DATEVALUE("02/04/92") = 8069 A3: 'Jul-94 =DATEVALUE(A3) = 8947 : This is the date number for 1-Jul-94 exponentiation equal to or greater than @SQRT Returns the square root on the number. @SQRT(34) -> 5.8310 @SQRT(@LOG(200)) -> 1.5169 @SQRT(@VAR(200,500,100)) -> 170 : This formula computes the standard deviation of the values given in the variance function. BLUECOLOR VA constant referring to BLUE for use in functions that change color (e.g. SETCOLOR()) @DEGTORAD lConverts num (in degrees) to radians. This complements the @RADTODEG() function. @DEGTORAD(180) -> 3.14159 equal to or less than Removes leading and trailing spaces from string. It also converts multiple consecutive spaces in string to single spaces. A3: Account # 3040302 40303 =TRIM(A3) -> Account # 3040302 40303 =TRIM(" Anything at all.") = Anything at all. link worksheets GENERALFORMAT aThis constant is used to specify the general format. =FORMAT(238.093, GENERALFORMAT) -> 238.093 @LEFT Returns a string containing the first num characters from the left of str. @LEFT("The first twelve characters",12) -> The first tw @LEFT("ABCDEFG",4) -> ABCD If exp is a string, returns exp; otherwise it returns a zero length string. This function is used to guarantee that an expression is a string. T(343) -> "" (blank string) A1: 2000 A2: Orders T(A1)&T(A2) = Orders @SETBKGCOLOR %Sets the background color of the current cell and returns value. Red, Green, and Blue are numbers between 0 and 1 that set the percentage of red, green, and blue that will be mixed together to create the color. White is all ones and black is all zeros. If any of the color components are out of bounds or result in errors, the color is not changed. @SETBKGCOLOR(A1,@IF(A1 > 50 #AND# A1 < 100,1,0),0,0) : This formula sets the background color to red if the number is greater than 50 and less than 100, otherwise sets the background color to black. @YELLOWCOLOR YA constant referring to YELLOW for use in functions that change color (e.g. @SETCOLOR()) ISNUMBER -Returns 1 if exp returns a numeric result. Returns 0 otherwise. =ISNUMBER(FIXED(300,0)) -> 0 : This formula converts the number "300" to a string using FIXED(). It then uses ISNUMBER() to test if that result is a value and returns 0 (false). =ISNUMBER(34) -> 1 A3: +394 - 100 =ISNUMBER(A3) -> 1 DIDCHANGE If exp is a number, N() returns exp, otherwise N() returns 0. N() is guaranteed to return a number and is used in functions that require numbers as parameters. N(STRING(300,0)) = 0 N(34) = 34 Returns the number of columns in a range. =COLS(A1:H20) -> 8 If RANGE1 is the name assigned to the range B1:CC200, =COLS(RANGE1) -> 80 @RAND EReturns a random number between 0 and 1,including 0 and excluding 1. @ISNA Returns 1 if exp returns an @NA error. Returns 0 otherwise. @ISNA(@NA) -> 1 (true) A1: 34 A2: 49 A3: @NA @ISNA(@SUM(A1:A3)) -> 1 : This formula returns 1 (true) because cell A3 contains @NA, which causes @SUM(A1:A3) to evaluate to @NA also. @DAVG Takes the average of numeric records that match criteria. Strings have a value of zero. This is a sample database: A B C D 1 NAME GRADE GPA GRADE 2 Joe B 3.2 B 3 Sue A 4.7 4 Robert C 2.7 5 Steve A 4.5 6 Ann B 4.2 The database range is A1:C6. The criteria range is D1:D2. To calculate the average GPA of everyone who earned a B use this formula: @DAVG(A1:C6,2,D1:D2) -> 3.7 @FIND ^Returns the location of sub-str in str. It starts searching for sub-str offset characters from the left of str. Returns !ERR if the string is not found. @FIND("hampton","Northampton",1) -> 5 @FIND("hampton","Northampton",6) -> !ERR (because it starts searching at the 5th character) A1: "The third word in this sentence." @FIND("word",A1,1) -> 10 @DAVE RANDOM greater than equal to less than @ISFORMULA BLACKCOLOR WA constant referring to BLACK for use in functions that change color (e.g. SETCOLOR()) ISERROR Returns 1 if exp returns an error other than @NA. Returns 0 otherwise. ISERROR(VALUE(``ABCD'') = 1 (true) : This formula tried to convert ``ABCD'' to a value using VALUE(). This returns !ERR, which causes ISERROR() to returns 1. ISERROR(@ERR) = 1 (true) @MONTH Returns the month for a date serial number. @CHOOSE(@MONTH(@NOW)-1,"Jan","Feb","Mar","Apr","May", "Jun","Jul","Aug","Sep","Oct","Nov","Dec") : This formula will display the current month. @MONTH(@DATEVALUE("12/24/92")) -> 12 SIGNAL HIf the condition is true, sends range of cells to the named Mach port. DOLLAR :Converts a number to a dollar format string. B3: 5251.769585987 =DOLLAR(B3,6) = $5251.769586 =DOLLAR(3/43,2)&" is now a string" -> 0.07 is now a string : This formula evaluates 3/43, then converts it to a dollar string with 2 decimal places of precision and concatenates it with the text " is now a string". division ERRORCONST subtraction addition HMSFORMAT rThis constant is used to specify date hours-minutes-seconds format. =FORMAT(9876.54321, HMSFORMAT) -> 1:02:13 PM multiplication @GETINPUT string concatenation LOWER ZConverts all the characters in str to lower case. LOWER(``Bill Johnson'') = bill johnson @RADTODEG hConverts num (in radians) to degrees. This complements the @DEGTORAD() function. @RADTODEG(@PI) -> 180 DSTDEV jReturns the standard deviation of the numeric records that match the criteria. This is a sample database: A B C D 1 STATE REGION AVG RF REGION 2 AL South 3.7 West 3 CA West 2.1 4 NJ East 5.2 5 FL South 4.9 6 LA South 6.8 7 TX West 5.0 8 MA North 7.6 9 AZ West 2.2 10 NV West 2.4 The database range is A1:C10. The criteria range is D1:D2. To find the standard deviation of rainfall in the Western region use this formula: =DSTDEV(A1:C10,2,D1:D2) -> 1.2029 @LENGTH NEXTYEAR Returns the next year of the date. If the date is 2/29 of a leap year, the date returned is 2/28 of the following year. =NEXTYEAR(NOW) : This formula returns a date-number one year from the current date. =NEXTYEAR(DATE(93,6,20)) -> 8936 : This is the date-number for 20-Jun-94. DEGTORAD kConverts num (in degrees) to radians. This complements the RADTODEG() function. =DEGTORAD(180) -> 3.14159 @WEEKDAY Returns the day of the week for a date serial number, 0 - Sunday through 6 - Saturday. @WEEKDAY(@NOW-5) : This returns the day of the week 5 days prior to today. A7: "6/20/82" @WEEKDAY(@DATEVALUE(A7)) -> 0 : Returns the day of the week for 20-Jun-82. @LTGRAYCOLOR ]A constant referring to Light GRAY for use in functions that change color (e.g. @SETCOLOR()) @YEAR Returns the year for a date serial number. @YEAR(@NOW-1000) : This returns the year of the date 1,000 days prior to the current date. A7: "6/20/82" @YEAR(@DATEVALUE(A7)) -> 82 : This formula extract the year from the date-string "6/20/82". /Returns the nth root of num. =ROOT(81,4) -> 3 DSTDDEV HISTOGRAM *Groups the values in range into #-of-divisions divisions, counts the number of items in each division. The formula should appear in #-of-divisions cells, starting at anchor. It returns the number of items in the division that is offset from the anchor point. Min and max are optional parameters that set the minimum and maximun values of the histogram range, so that the distribution will only be calculated for values between min and max. A B 1 Name GPA 2 Joe 2.6 3 Sue 3.7 4 Robert 3.2 5 Steve 3.0 6 Ann 3.8 7 John 3.5 8 Mary 2.9 9 Chris 3.5 To divide the students into 5 divisions by GPA and determine how many fall into each division: A10: =HISTOGRAM($B$2:$B$9,5,$A$10) -> 1 B10: =SAME($A$10) -> 2 C10: =SAME($A$10) -> 1 D10: =SAME($A$10) -> 2 E10: =SAME($A$10) -> 2 RANGE Returns the address for the range specified by the upper row, left column, lower row, right column.. RANGE(0,0,1,1) is the range a1:b2. Adds all the numbers or sums the numbers in the range. You can specify as many numbers or ranges as you want. =SUM(300,400,2300,100) -> 3100 A1: Blue A2: 300 A4: 900 B1: 1200 =SUM(A1:A4,B1) -> 2400 @TIME Returns a date serial number for a given time. @TIME(11,30,00) -> 0.479167 : This is the date-number for 11:30 AM. @NOW+@TIME(8,0,0) : This formula returns a date-number for the time 8 hours from now. $381,595 To figure out the value of the project: Project Value = Initial Cost + NPV +A1+@NPV(7%,A2:A6) -> -18,404 No, this project is not worthwhile. Note: This differs from NPV(rate,pmt1[,pmt2[,...]]). @NPV() treats string values as zero, but NPV() ignores string values. Returns the payment to pay off a loan of prin with interest int over pers payments. What would be the monthly payment on an $40,000 home improvement loan, to be repaid in 5 years at a fixed rate of 9%? @PMT(40000,9%/12,5*12) -> $830.33 per month. Returns the maximum numeric value of the records in database that match criteria. This is a sample database: A B C D 1 NAME SALES REGION REGION 2 Joe 3000 East East 3 Sue 4500 North 4 Robert 2800 East 5 Steve 3700 South 6 Ann 2400 West The database range is A1:C6. The criteria range is D1:D2. To find the largest sales figure in the Eastern region use this formula: =DMAX(A1:C6,1,D1:D2) = 3000 Returns the remainder of num divided by div @MOD(12,5) -> 2 @NOW-@MOD(@NOW,7)+5 : This function returns the date-number for Monday of the current week. Returns the largest number in the range or list. Strings are considered to be zero. @MAX(4.5,3.2,2.5,2.5,6.2) = 6.2 @MAX(4,4,4) = 4 A B C 1 300 400 2 0 400 3 200 100 4 300 5000 5 700 6 600 7 300 8 500 @MAX(A1:A8,C1:C8) -> 5000 Note: This function differs from MAX(num|range[,...]). @MAX() treats string values as zero, but MAX() ignores string values. FALSE @DMYFORMAT oThis constant is used to specify the date day-month-year format. @FORMAT(9876.54321, @DMYFORMAT) -> 15-Jan-97 ScrollingText Funtcions Desciptions Panel File's Owner Button1 [26@] IBOutletConnector IBConnector IBControlConnector changeList: selectedItem: delegate pasteItem: window nameStrings aboutStrings IBHelpConnector $MesaMenus/Main/Edit/FormBuilder.rtfd Formula Builder command